<?php
session_start();
include("../include/util.inc.php");

function createTable()
{
    $PDO = getPDO();
    $sql = "CREATE TABLE question(
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        category VARCHAR(255) NOT NULL,
        level INT NOT NULL,
        question VARCHAR(255) NOT NULL,
        answer VARCHAR(255) NOT NULL,
        property INT NOT NULL default 0
        );

        CREATE TABLE user(
            uid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL,
            password VARCHAR(255) NOT NULL
        );

        CREATE TABLE history(
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            hdate DATETIME NOT NULL,
            uid INT NOT NULL,
            hid VARCHAR(255) NOT NULL,
            hcategory VARCHAR(255) NOT NULL,
            hlevel INT NOT NULL,
            hsize INT NOT NULL,
            hscore INT NOT NULL,
            htimelimit INT NOT NULL
        );

        CREATE TABLE detail(
            id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            hid VARCHAR(255) NOT NULL,
            dquestion VARCHAR(255) NOT NULL,
            danswer VARCHAR(255) NOT NULL,
            dstate VARCHAR(255) NOT NULL
        )";
    $PDO->exec($sql);
    echo "数据表创建成功<br>";
}

//csv文件导入数据库
function importmysql($filepath)
{
    $PDO = getPDO();
    $handle = new SplFileObject($filepath, 'r+');
    while ($line = $handle->fgets()) {
        $content = explode("#", $line);
        switch ($content[1]) {
            case "easy":
                $level = 1;
                break;
            case "medium":
                $level = 2;
                break;
            case "hard":
                $level = 3;
                break;
        }
        $stmt = $PDO->prepare("INSERT INTO quizme.question(`category`,`level`,`question`,`answer`) VALUES (?,?,?,?);");
        $stmt->execute([$content[0], $level, $content[2], $content[3]]);
    }
    echo 'import successfully';
}

try {
    createDB();
    echo 'created successfully' . PHP_EOL;
} catch (\PDOException $e) {
    echo $e->getMessage(). PHP_EOL;
}

try{
    createTable();
    importmysql('../database.csv');
    echo 'create table successfully'  . PHP_EOL;
}catch (Exception $e) {
    echo $e->getMessage();
}

?>